package tau.pimpmyfreedb.dal;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;

import tau.pimpmyfreedb.common.types.Album;
import tau.pimpmyfreedb.common.types.Artist;
import tau.pimpmyfreedb.common.types.FBLike;
import tau.pimpmyfreedb.common.types.MusicItem;
import tau.pimpmyfreedb.common.types.PagedSearchResult;
import tau.pimpmyfreedb.common.types.PimpFBFriend;
import tau.pimpmyfreedb.common.types.Track;
import tau.pimpmyfreedb.common.types.TrackLength;
import tau.pimpmyfreedb.common.types.Tuple;
import tau.pimpmyfreedb.common.types.User;

public class DALController implements IDalController {
    protected static final int NameLengthInDB = 255;
    private Connection connection;

    enum OperationType {
	INSERT, DELETE, UPDATE
    }

    public DALController() throws ClassNotFoundException {
	Class.forName("com.mysql.jdbc.Driver");
    }

    /*
     * Section 1: Connection
     */

    @Override
    public void connect(String url, String username, String password)
	    throws SQLException {
	System.out.printf("RealDAL: Connecting to %s@%s\n", username, url);
	connection = DriverManager.getConnection(url, username, password);
	System.out.println("RealDAL: Connected.");
    }

    @Override
    public void disconnect() throws SQLException {
	connection.close();
    }

    @Override
    public boolean isConnected() {
	if (connection == null)
	    return false;

	try {
	    return !connection.isClosed();
	} catch (SQLException e) {
	    return false;
	}
    }

    /*
     * Section 2: Query
     */

    @Override
    public PagedSearchResult<IDataItem[]> getData(DataFilter filter)
	    throws SQLException {

	// Get the query
	PreparedStatement stmt = createQueryForFilter(filter, false);
	// Execute and parse the query
	IDataItem[] items = queryAndParse(filter.getReturnType(), stmt)
		.toArray(new IDataItem[0]);
	stmt.close();

	int totalResults = items.length;

	if (filter.isUsePaging() && filter.getPageSize() == items.length) {
	    PreparedStatement countStmt = createQueryForFilter(filter, true);

	    totalResults = getTotalResults(countStmt);
	    countStmt.close();
	}

	System.out.printf("DAL: Returning %d items out of %d.\n", items.length,
		totalResults);
	return new PagedSearchResult<IDataItem[]>(items, filter.getPageIndex(),
		totalResults);
    }

    private PreparedStatement createQueryForFilter(DataFilter filter,
	    boolean makeCountQuery) throws SQLException {
	DataType requestedType = filter.getReturnType();

	// NOTE: StringBuffer could've been used, but it seems that the
	// performance gain is neglectable in comparison to DB access times.

	// Fill defaults
	String tableName = getTableNameForType(requestedType);
	String selectColumns = makeCountQuery ? String.format("COUNT(%s_id)",
		tableName) : "*";
	String selectFrom = tableName;
	String whereConditions = "";
	String orderBy = "name";

	// Start building the query.

	if (filter.isRestrictByItem()) {
	    // Deal with restrict by item -- e.g. Get tracks for specific
	    // album, or get likes for a specific facebook friend.
	    DataType restrictByItemType = filter.getRestrictByItemType();
	    int restrictByItemID = filter.getRestrictByItemID();
	    String restrictByTable = getTableNameForType(restrictByItemType);

	    if (isOneToManyRelation(requestedType, restrictByItemType)) {
		// Simple filter by related item:
		// The cases where the requested items contain a reference
		// to the restrict-by-item
		// (For example, a Facebook-like is related to a single
		// Facebook friend.)
		// e.g. SELECT * FROM track WHERE album_id=27
		whereConditions = String.format("%s_id=%d", restrictByTable,
			restrictByItemID);
	    } else {
		// Many-to-many relation.

		// A join is needed, for example:
		// SELECT track.* FROM track, track_artist
		// WHERE track.track_id = track_artist.track_id AND
		// track_artist.artist_id = 50

		String relationsTableName = getRelationsTableName(
			requestedType, restrictByItemType);

		selectColumns = makeCountQuery ? "COUNT(*)" : String.format(
			"%s.*", tableName);
		selectFrom = String.format("%s,%s", tableName,
			relationsTableName);
		whereConditions = String.format(
			"((%s.%s_id = %s.%s_id) AND (%s.%s_id = %d))",
			tableName, tableName, relationsTableName, tableName,
			relationsTableName, restrictByTable, restrictByItemID);
		orderBy = String.format("%s.name", tableName);
	    }
	} else if (requestedType == DataType.TRACK && filter.isTextSearch()) {
	    // Special case: for track, full index search is supported using a
	    // MyISAM table.
	    // So for text searches in tracks, this query is used.

	    if (makeCountQuery) {
		selectFrom = "track_text";
		selectColumns = "COUNT(track_id)";
		whereConditions = "(name LIKE ? OR name LIKE ?)";
	    } else {
		selectFrom = "track,track_text";
		selectColumns = "track.*";
		whereConditions = "(track.track_id = track_text.track_id AND "
			+ "(track_text.name LIKE ? OR track_text.name LIKE ?))";
		orderBy = "track_text.name";
	    }
	} else if (filter.isTextSearch()) {
	    // Regular text search
	    if (filter.isSearchExact()) {
		whereConditions = "(name = ?)";
	    } else {
		whereConditions = "(name LIKE ? OR name LIKE ?)";
	    }
	} else if (filter.isSpecificID()) {
	    // Regular search by id: track.track_id=225
	    whereConditions = String.format("%s.%s_id=%d", tableName,
		    tableName, filter.getDataID());
	}

	String query = String.format("SELECT %s FROM %s", selectColumns,
		selectFrom);
	if (whereConditions.length() > 0)
	    query += String.format(" WHERE %s", whereConditions);

	if (!makeCountQuery)
	    query += String.format(" ORDER BY %s", orderBy);

	if (filter.getPageSize() > -1 && !makeCountQuery) {
	    int firstResult = filter.getPageIndex() * filter.getPageSize();
	    query += String.format(" LIMIT %d,%d", firstResult,
		    filter.getPageSize());
	}
	System.out.println(query);

	// Use a PreparedStatement to prevent SQL injection attacks from the
	// search input!!
	PreparedStatement pstmt = connection.prepareStatement(query);
	if (filter.isTextSearch()) {
	    if (filter.isSearchExact()) {
		// This is currently only used with artists, when looking for
		// IDs of
		// artists by name.
		pstmt.setString(1, filter.getSearchString());
	    } else {
		// Search for prefixes of words -- either first word, or a word
		// in the middle
		// WHERE name LIKE 'Brit%' OR NAME LIKE '% Brit%'
		// will find Britney Spears but will not find Gambrit.
		pstmt.setString(1, filter.getSearchString() + "%");
		pstmt.setString(2, "% " + filter.getSearchString() + "%");
	    }
	}
	return pstmt;
    }

    private ArrayList<IDataItem> queryAndParse(DataType requestedType,
	    PreparedStatement pstmt) throws SQLException {
	ResultSet rs = pstmt.executeQuery();
	ArrayList<IDataItem> items = new ArrayList<IDataItem>();
	while (rs.next()) {
	    IDataItem item = parseItem(requestedType, rs);
	    if (item != null) {
		items.add(item);
	    }
	}
	return items;
    }

    private IDataItem parseItem(DataType requestedType, ResultSet rs)
	    throws SQLException {
	IDataItem item = null;
	switch (requestedType) {
	case ALBUM:
	    item = parseAlbum(rs);
	    break;
	case ARTIST:
	    item = parseArtist(rs);
	    break;
	case FB_FRIEND:
	    item = parseFBFriend(rs);
	    break;
	case PIMP_USER:
	    item = parseUser(rs);
	    break;
	case TRACK:
	    item = parseTrack(rs);
	    break;
	case FB_LIKE:
	    item = parseLike(rs);
	    break;
	}
	if (item == null) {
	    throw new IllegalArgumentException("Type can't be parsed.");
	}
	return item;
    }

    private int getTotalResults(PreparedStatement stmt) throws SQLException {
	ResultSet rs = stmt.executeQuery();
	rs.next();
	return rs.getInt(1);
    }

    /*
     * Section 3: Insert / Update / Delete
     */

    @Override
    public DalResponse insertData(IDataItem[] data) throws SQLException {
	System.out.println("RealDAL: Inserting data...");
	return performDBOperation(data, OperationType.INSERT);
    }

    @Override
    public DalResponse updateData(IDataItem[] data) throws SQLException {
	System.out.println("RealDAL: Inserting data...");
	return performDBOperation(data, OperationType.UPDATE);
    }

    @Override
    public DalResponse deleteData(IDataItem[] data) throws SQLException {
	return performDBOperation(data, OperationType.DELETE);
    }

    private DalResponse performDBOperation(IDataItem[] data,
	    OperationType operationType) throws SQLException {
	boolean disableForeignKeyChecks = (operationType == OperationType.INSERT);
	connection.setAutoCommit(false);
	if (disableForeignKeyChecks) {
	    // Disable foreign keys check (only if Operation==INSERT)
	    setForeignKeyChecks(false);
	}

	int executedStatementsCount = 0;
	// 1. For batch execution, we first group the data by types.
	HashMap<DataType, ArrayList<IDataItem>> listsByDataType = groupItemsByType(data);
	// 2. Execute batch for each type
	for (DataType type : DataType.values()) {
	    executedStatementsCount += performOperationForType(operationType,
		    listsByDataType.get(type), type);
	}
	// 3. Execute the transaction
	if (executedStatementsCount > 0) {
	    connection.commit();
	}
	if (disableForeignKeyChecks) {
	    // Re-enable foreign keys check
	    setForeignKeyChecks(true);
	}
	System.out.println("RealDAL: Done with operation.");
	return new DalResponse(true, executedStatementsCount, "Done stuff.");
    }

    private HashMap<DataType, ArrayList<IDataItem>> groupItemsByType(
	    IDataItem[] data) {
	HashMap<DataType, ArrayList<IDataItem>> listsByDataType = new HashMap<DataType, ArrayList<IDataItem>>();
	for (DataType type : DataType.values()) {
	    listsByDataType.put(type, new ArrayList<IDataItem>());
	}
	for (IDataItem item : data) {
	    listsByDataType.get(item.getDataType()).add(item);
	}
	return listsByDataType;
    }

    private int performOperationForType(OperationType operationType,
	    ArrayList<IDataItem> list, DataType type) throws SQLException {
	if (list.size() == 0)
	    return 0;
	boolean autoGeneratedKeys = (operationType == OperationType.INSERT);
	// 1. Get the relevant command
	String command = getCommandForOperation(operationType, type);
	System.out.println(command);
	// 2. Prepare the statement for the batch
	PreparedStatement pstmt = connection.prepareStatement(command,
		autoGeneratedKeys ? Statement.RETURN_GENERATED_KEYS
			: Statement.NO_GENERATED_KEYS);
	// 3. For each item, set its details to the statement and add to batch
	for (IDataItem item : list) {
	    fillItemDetails(operationType, pstmt, item);
	    pstmt.addBatch();
	}
	// 4. Execute the batch
	int[] results = pstmt.executeBatch();
	// 5. Set the IDs after insert.
	if (autoGeneratedKeys) {
	    // On insert operations, the generated IDs are returned
	    // and the item objects are updated.
	    ArrayList<Integer> keys = getGeneratedKeys(pstmt);
	    pstmt.close();
	    setDataIDsAfterInsert(keys, list, results);
	} else {
	    pstmt.close();
	}
	return results.length;
    }

    private String getCommandForOperation(OperationType operationType,
	    DataType type) {
	String command = null;
	switch (operationType) {
	case DELETE:
	    command = getDeleteCommand(type);
	    break;
	case INSERT:
	    command = getInsertCommand(type);
	    break;
	case UPDATE:
	    command = getUpdateCommand(type);
	    break;
	}
	return command;
    }

    private void fillItemDetails(OperationType operationType,
	    PreparedStatement pstmt, IDataItem item) throws SQLException {
	if (item.getName().length() > NameLengthInDB) {
	    item.setName(item.getName().substring(0, NameLengthInDB));
	}
	int nextColumnIndex = 1;
	if (operationType == OperationType.UPDATE
		|| operationType == OperationType.INSERT) {
	    switch (item.getDataType()) {
	    case ALBUM:
		nextColumnIndex = fillAlbumDetails(pstmt, (Album) item);
		break;
	    case ARTIST:
		nextColumnIndex = fillArtistDetails(pstmt, (Artist) item);
		break;
	    case FB_FRIEND:
		nextColumnIndex = fillFriendDetails(pstmt, (PimpFBFriend) item);
		break;
	    case FB_LIKE:
		nextColumnIndex = fillLikeDetails(pstmt, (FBLike) item);
		break;
	    case PIMP_USER:
		nextColumnIndex = fillUserDetails(pstmt, (User) item);
		break;
	    case TRACK:
		nextColumnIndex = fillTrackDetails(pstmt, (Track) item);
		break;
	    default:
		throw new IllegalArgumentException(
			"Can't fill item details for type");
	    }
	}
	if (operationType == OperationType.UPDATE
		|| operationType == OperationType.DELETE) {
	    pstmt.setInt(nextColumnIndex, item.getDataId());
	}
    }

    private void setDataIDsAfterInsert(ArrayList<Integer> keys,
	    ArrayList<IDataItem> items, int[] executeResults)
	    throws SQLException {
	int nextKeyIndex = 0;
	for (int i = 0; i < items.size(); i++) {
	    IDataItem item = items.get(i);
	    if (executeResults[i] > 0) {
		// Simple case: an ID was generated
		if (nextKeyIndex >= keys.size()) {
		    throw new SQLException("Didn't get ID's for all items.");
		}
		int id = keys.get(nextKeyIndex);
		item.setDataIdAfterInsert(id);
		nextKeyIndex++;
	    } else {
		// Harder case: After an INSERT IGNORE operation, a key wasn't
		// generated.
		// So in order to fill the ID, we need to retrieve the existing
		// item from the DB.
		IDataItem existingItem = getItemByName(item.getDataType(),
			item.getName());
		item.setDataIdAfterInsert(existingItem.getDataId());
	    }
	}
    }

    private String getDeleteCommand(DataType type) {
	String tableName = getTableNameForType(type);
	return String.format("DELETE FROM %s WHERE %s_id = ?", tableName,
		tableName);
    }

    private String getInsertCommand(DataType type) {
	String command;
	switch (type) {
	case ALBUM:
	    command = "INSERT INTO album(name, year, genre, extended_data) VALUES(?,?,?,?)";
	    break;
	case ARTIST:
	    // Artist has UNIQUE names, so in case an artist already exists, it
	    // is not inserted.
	    command = "INSERT IGNORE INTO artist(name, extended_data) VALUES(?,?)";
	    break;
	case FB_FRIEND:
	    command = "INSERT INTO fb_friend(name, user_id) VALUES(?,?)";
	    break;
	case FB_LIKE:
	    command = "INSERT INTO fb_like(name, fb_friend_id) VALUES(?,?)";
	    break;
	case PIMP_USER:
	    command = "INSERT INTO user(name, fb_token) VALUES(?,?)";
	    break;
	case TRACK:
	    command = "INSERT INTO track(name, album_id, length, extended_data) VALUES(?,?,?,?)";
	    break;
	default:
	    throw new IllegalArgumentException("No insert command for type.");
	}
	return command;
    }

    private String getUpdateCommand(DataType type) {
	String tableName = getTableNameForType(type);
	String fields;
	switch (type) {
	case ALBUM:
	    fields = "name=?, year=?, genre=?, extended_data=?";
	    break;
	case ARTIST:
	    fields = "name=?, extended_data=?";
	    break;
	case TRACK:
	    fields = "name=?, album_id=?, length=?, extended_data=?";
	    break;
	default:
	    throw new IllegalArgumentException("No insert command for type.");
	}
	return String.format("UPDATE %s SET %s WHERE %s_id=?", tableName,
		fields, tableName);
    }

    @Override
    public DalResponse insertRelations(ArrayList<DataItemRelation> relations)
	    throws IllegalArgumentException, SQLException {
	RelationsByTypes byTypes = groupRelationsByTypes(relations);
	connection.setAutoCommit(false);
	int insertedRelationsCount = 0;
	
	// Run a batch for each type of relation
	insertedRelationsCount += insertGenericRelations(insertedRelationsCount,
		DataType.ARTIST, DataType.ALBUM, byTypes.artistAlbumRelations);
	insertedRelationsCount += insertGenericRelations(
		insertedRelationsCount, DataType.ARTIST, DataType.TRACK,
		byTypes.artistTrackRelations);
	
	// Album/track isn't a generic relation: Album is a FK in Track.
	insertedRelationsCount += updateAlbumTrackRelations(byTypes.albumTrackRelations);
	
	if (insertedRelationsCount > 0) {
	    connection.commit();
	}
	return new DalResponse(true, insertedRelationsCount, "Wow. Wo-ow.");
    }

    @Override
    public DalResponse deleteRelations(ArrayList<DataItemRelation> relations)
	    throws SQLException {
	RelationsByTypes byTypes = groupRelationsByTypes(relations);
	connection.setAutoCommit(false);
	
	// Run a batch for each type of relation
	deleteRelations(byTypes.artistTrackRelations, DataType.ARTIST,
		DataType.TRACK);
	deleteRelations(byTypes.artistAlbumRelations, DataType.ARTIST,
		DataType.ALBUM);
	
	connection.commit();

	return new DalResponse(true, relations.size(), "Hooray");
    }

    private class RelationsByTypes {
	ArrayList<Tuple<Integer, Integer>> artistAlbumRelations = new ArrayList<Tuple<Integer, Integer>>();
	ArrayList<Tuple<Integer, Integer>> artistTrackRelations = new ArrayList<Tuple<Integer, Integer>>();
	ArrayList<Tuple<Integer, Integer>> albumTrackRelations = new ArrayList<Tuple<Integer, Integer>>();
    }

    private RelationsByTypes groupRelationsByTypes(
	    ArrayList<DataItemRelation> relations) {
	RelationsByTypes byTypes = new RelationsByTypes();
	for (DataItemRelation relation : relations) {
	    if (relation.getItem1().getDataType() == DataType.ARTIST
		    && relation.getItem2().getDataType() == DataType.ALBUM) {
		addRelationToLocalList(byTypes.artistAlbumRelations, relation);
	    } else if (relation.getItem1().getDataType() == DataType.ARTIST
		    && relation.getItem2().getDataType() == DataType.TRACK) {
		addRelationToLocalList(byTypes.artistTrackRelations, relation);
	    } else if (relation.getItem1().getDataType() == DataType.ALBUM
		    && relation.getItem2().getDataType() == DataType.TRACK) {
		addRelationToLocalList(byTypes.albumTrackRelations, relation);
	    } else {
		throw new IllegalArgumentException(
			"Invalid relation in addRelations");
	    }
	}
	return byTypes;
    }

    private int insertGenericRelations(int insertedRelationsCount,
	    DataType type1, DataType type2,
	    ArrayList<Tuple<Integer, Integer>> relationsByType)
	    throws SQLException {
	if (relationsByType.size() == 0)
	    return 0;

	int executed = 0;
	String relationsTable = getRelationsTableName(type1, type2);
	String command = String.format(
		"INSERT IGNORE INTO %s(%s_id, %s_id) VALUES(?,?)",
		relationsTable, getTableNameForType(type1),
		getTableNameForType(type2));
	System.out.println(command);
	PreparedStatement pstmt = connection.prepareStatement(command);
	for (Tuple<Integer, Integer> ids : relationsByType) {
	    pstmt.setInt(1, ids.getX());
	    pstmt.setInt(2, ids.getY());
	    pstmt.addBatch();
	}
	executed += pstmt.executeBatch().length;
	pstmt.close();
	return executed;
    }

    private int updateAlbumTrackRelations(
	    ArrayList<Tuple<Integer, Integer>> albumTrackIDs)
	    throws SQLException {
	if (albumTrackIDs.size() == 0)
	    return 0;
	
	int executed = 0;
	PreparedStatement pstmt = connection.prepareStatement(String
		.format("UPDATE track SET album_id = ? WHERE track_id = ?"));
	for (Tuple<Integer, Integer> ids : albumTrackIDs) {
	    pstmt.setInt(1, ids.getX());
	    pstmt.setInt(2, ids.getY());
	    pstmt.addBatch();
	}
	executed += pstmt.executeBatch().length;
	pstmt.close();
	return executed;
    }

    private void deleteRelations(ArrayList<Tuple<Integer, Integer>> toDelete,
	    DataType type1, DataType type2) throws SQLException {
	if (toDelete.size() > 0) {
	    PreparedStatement pstmt = prepareDeleteRelationStatement(type1,
		    type2);
	    for (Tuple<Integer, Integer> relation : toDelete) {
		pstmt.setInt(1, relation.getX());
		pstmt.setInt(2, relation.getY());
		pstmt.addBatch();
	    }
	    pstmt.execute();
	    pstmt.close();
	}
    }

    private PreparedStatement prepareDeleteRelationStatement(DataType type1,
	    DataType type2) throws SQLException {
	String tableName = getRelationsTableName(type1, type2);
	String type1Name = getTableNameForType(type1);
	String type2Name = getTableNameForType(type2);
	// Something like:
	// DELETE FROM artist_album WHERE artist_id = 234 AND album_id = 124
	PreparedStatement pstmt = connection.prepareStatement(String.format(
		"DELETE FROM %s WHERE %s_id = ? AND %s_id = ?", tableName,
		type1Name, type2Name));
	return pstmt;
    }

   /*
    * Fill details, type specific methods -- Used for INSERT and UDPATE commands.
    */

    private int fillAlbumDetails(PreparedStatement pstmt, Album album)
	    throws SQLException {
	pstmt.setString(1, album.getName());
	int year = album.getYear();
	if (year != Album.UKNOWN_YEAR) {
	    pstmt.setInt(2, year);
	} else {
	    pstmt.setNull(2, java.sql.Types.SMALLINT);
	}
	pstmt.setString(3, album.getGenre());
	pstmt.setString(4, album.getExtendedData());
	return 5;
    }

    private int fillArtistDetails(PreparedStatement pstmt, Artist artist)
	    throws SQLException {
	pstmt.setString(1, artist.getName());
	pstmt.setString(2, artist.getExtendedData());
	return 3;
    }

    private int fillTrackDetails(PreparedStatement pstmt, Track track)
	    throws SQLException {
	pstmt.setString(1, track.getName());
	if (track.getAlbumID() != MusicItem.NewItemId) {
	    pstmt.setInt(2, track.getAlbumID());
	} else {
	    pstmt.setNull(2, java.sql.Types.SMALLINT);
	}
	pstmt.setInt(3, track.getLength().getTotalSeconds());
	pstmt.setString(4, track.getExtendedData());
	return 5;
    }

    private int fillUserDetails(PreparedStatement pstmt, User user)
	    throws SQLException {
	pstmt.setString(1, user.getName());
	pstmt.setString(2, user.getFbToken());
	return 3;
    }

    private int fillFriendDetails(PreparedStatement pstmt, PimpFBFriend friend)
	    throws SQLException {
	pstmt.setString(1, friend.getName());
	pstmt.setInt(2, friend.getRelatedUserID());
	return 3;
    }

    private int fillLikeDetails(PreparedStatement pstmt, FBLike like)
	    throws SQLException {
	pstmt.setString(1, like.getName());
	pstmt.setInt(2, like.getRelatedFBFriendID());
	return 3;
    }
    
    /*
     * Parse details, type specific methods -- Used for SELECT commands.
     */

    private IDataItem parseUser(ResultSet rs) throws SQLException {
	int id = rs.getInt(1);
	String name = rs.getString(2);
	String fbToken = rs.getString(3);
	User user = new User(id, name, fbToken);
	return user;
    }

    private IDataItem parseTrack(ResultSet rs) throws SQLException {
	int id = rs.getInt(1);
	String name = rs.getString(2);
	int albumID = rs.getInt(3);
	TrackLength length = new TrackLength(rs.getInt(4));
	String extendedData = rs.getString(5);
	Track track = new Track(id, name, albumID, length, extendedData);
	return track;
    }

    private IDataItem parseFBFriend(ResultSet rs) throws SQLException {
	int id = rs.getInt(1);
	String name = rs.getString(2);
	int userID = rs.getInt(3);
	PimpFBFriend fbFriend = new PimpFBFriend(id, name, userID);
	return fbFriend;
    }

    private IDataItem parseLike(ResultSet rs) throws SQLException {
	int id = rs.getInt(1);
	String name = rs.getString(2);
	int fbFriendID = rs.getInt(3);
	return new FBLike(id, name, fbFriendID);
    }

    private IDataItem parseArtist(ResultSet rs) throws SQLException {
	int id = rs.getInt(1);
	String name = rs.getString(2);
	String extendedData = rs.getString(3);
	return new Artist(id, name, extendedData);
    }

    private IDataItem parseAlbum(ResultSet rs) throws SQLException {
	int id = rs.getInt(1);
	String name = rs.getString(2);
	int year = rs.getInt(3);
	String genre = rs.getString(4);
	String extendedData = rs.getString(5);
	return new Album(id, name, genre, year, extendedData);
    }

    /*
     * General helper functions.
     */
    
    private void setForeignKeyChecks(boolean foreignKeyChecks)
            throws SQLException {
        Statement stmt = connection.createStatement();
        stmt.execute(String.format("SET FOREIGN_KEY_CHECKS=%d",
        	foreignKeyChecks ? 1 : 0));
        stmt.close();
    }

    private String getRelationsTableName(DataType requestedType,
            DataType restrictByItemType) {
        HashSet<DataType> types = new HashSet<DataType>();
        types.add(requestedType);
        types.add(restrictByItemType);
    
        if (types.contains(DataType.ARTIST) && types.contains(DataType.TRACK))
            return "track_artist";
        if (types.contains(DataType.ARTIST) && types.contains(DataType.ALBUM))
            return "album_artist";
        throw new IllegalArgumentException("Not implemented for types.");
    }

    private String getTableNameForType(DataType requestedType) {
        String requestedTypeTable = null;
        switch (requestedType) {
        case ALBUM:
            requestedTypeTable = "album";
            break;
        case ARTIST:
            requestedTypeTable = "artist";
            break;
        case FB_FRIEND:
            requestedTypeTable = "fb_friend";
            break;
        case FB_LIKE:
            requestedTypeTable = "fb_like";
            break;
        case PIMP_USER:
            requestedTypeTable = "user";
            break;
        case TRACK:
            requestedTypeTable = "track";
            break;
        }
        return requestedTypeTable;
    }

    private IDataItem getItemByName(DataType type, String name)
	    throws SQLException {
	DataFilter filter = new DataFilter();
	filter.setReturnType(type);
	filter.setSearchString(name, true);
	IDataItem[] result = getData(filter).getResult();
	if (result.length != 1) {
	    throw new SQLException(
		    "Item didn't get inserted, but no existing item with same name was found.");
	}
	IDataItem item = result[0];
	return item;
    }
    
    private ArrayList<Integer> getGeneratedKeys(PreparedStatement pstmt)
	    throws SQLException {
	ArrayList<Integer> keys = new ArrayList<Integer>();
	ResultSet rs = pstmt.getGeneratedKeys();
	while (rs.next()) {
	    keys.add(rs.getInt(1));
	}
	return keys;
    }

    private void addRelationToLocalList(
            ArrayList<Tuple<Integer, Integer>> list, DataItemRelation relation) {
        list.add(new Tuple<Integer, Integer>(relation.getItem1().getDataId(),
        	relation.getItem2().getDataId()));
    }

    private boolean isOneToManyRelation(DataType requestedType,
            DataType restrictByItemType) {
        boolean isManyToOneRelation = (requestedType == DataType.FB_FRIEND
        	|| requestedType == DataType.FB_LIKE || (requestedType == DataType.TRACK && restrictByItemType == DataType.ALBUM));
        return isManyToOneRelation;
    }
}
